package com.apware.utils.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.nio.charset.Charset;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jumpmind.symmetric.csv.CsvReader;

import com.apware.utils.config.Config;
import com.apware.utils.entity.CarPartsInfo;

import jxl.Cell;
import jxl.Workbook;
import jxl.WorkbookSettings;
/**
 * 世德
 * @author Administrator
 *
 */
public class SDCarPartsInfoReader {

	static Logger log = Logger.getLogger(SDCarPartsInfoReader.class);

	//jingrui excel format
	String excelFilePath = Config.exportedFile + ".xls";
	public static final Integer PARTS_NO = 0;
	public static final Integer STANDARD_PARTS_NO = 1;
	public static final Integer PARTS_NAME = 2;
	public static final Integer IS_OEM = 3;
	public static final Integer BRAND = 4;
	public static final Integer PARTS_PRODUCING = 5;
	public static final Integer PARTS_CAR = 6;
	public static final Integer PARTS_UNIT = 7;
	public static final Integer PARTS_STOCK = 8;
	public static final Integer PARTS_BATCH_PRICE = 9;
	public static final Integer PARTS_PRICE  = 10;
	public static final Integer PARTS_WAREHOUSE =11;
	
	
	

	//poi reader
		public List<CarPartsInfo> getCarPartsList() {
			List<CarPartsInfo> list = new ArrayList<CarPartsInfo>();
			Config.readConfig();
			excelFilePath = Config.exportedFile + ".xls";
	        FileInputStream inputStream = null;
	        File f=null;
			try {
				 f = new File("excelFilePath");
				System.out.println(f.getCanonicalPath());
				if(!f.exists()) return null;
				inputStream = new FileInputStream(f);
				
			} catch (FileNotFoundException e1) {
				log.error(e1);
				return null;
			} catch (IOException e) {
				log.error(e);
			}
	         
			org.apache.poi.ss.usermodel.Workbook workbook = null;
			try {
				POIFSFileSystem poi=new	POIFSFileSystem(inputStream);
				workbook = new HSSFWorkbook(poi);
			} catch (IOException e) {
				log.error(e);
				return null;
			}
			org.apache.poi.ss.usermodel.Sheet firstSheet = workbook.getSheetAt(0);
	        Iterator<Row> iterator = firstSheet.iterator();
	        
	
	        while (iterator.hasNext()) {
	            Row nextRow = iterator.next();
	            CarPartsInfo c = new CarPartsInfo();
	            if(nextRow.getCell(0) != null && nextRow.getCell(0).getStringCellValue().equals("partno"))
	            		continue;
	            if(nextRow.getCell(PARTS_NO).getStringCellValue() != null ) {
		            c.setPartsNo(nextRow.getCell(PARTS_NO).getStringCellValue());
		            if(nextRow.getCell(PARTS_NO) != null)
	        			c.setStandardPartsNo(nextRow.getCell(PARTS_NO).getStringCellValue());
		            
		            if(nextRow.getCell(PARTS_NAME) != null)
		            		c.setCname(nextRow.getCell(PARTS_NAME).getStringCellValue());
		            c.setEname(null);
		            c.setIsOem("0");
		        
					if(nextRow.getCell(BRAND) != null) {
	            			c.setManufactureCname(nextRow.getCell(BRAND).getStringCellValue().equals("")?"奔驰原厂":nextRow.getCell(BRAND).getStringCellValue());
	            			c.setManufactureEname(null);
					}
					if(nextRow.getCell(PARTS_PRODUCING) != null) { 
						c.setProducingArea(nextRow.getCell(PARTS_PRODUCING).getStringCellValue());
					}
					
					if(nextRow.getCell(PARTS_CAR) != null) 
						c.setCarInfo(nextRow.getCell(PARTS_CAR).getStringCellValue());
					
					if(nextRow.getCell(PARTS_UNIT) != null)
	            			c.setUnit(nextRow.getCell(PARTS_UNIT).getStringCellValue());
					
					if(nextRow.getCell(PARTS_STOCK) != null)
						c.setStock((long) nextRow.getCell(PARTS_STOCK).getNumericCellValue());
					if(nextRow.getCell(PARTS_BATCH_PRICE) != null) 
	            			c.setBatchPrice(nextRow.getCell(PARTS_BATCH_PRICE).getNumericCellValue());
					if(nextRow.getCell(PARTS_PRICE) != null) 
						c.setPrice(nextRow.getCell(PARTS_PRICE).getNumericCellValue()); 
					c.setCostPrice(0);
					c.setOwnNo(nextRow.getCell(PARTS_WAREHOUSE).getStringCellValue());
//					System.out.println(c.toString());
					list.add(c);
	            }
	        }
	         
	        try {
				workbook.close();
		        inputStream.close();
		        archiveXls();
		    
			} catch (IOException e) {
				log.error(e);
			}
			return list;
		}

    /**
     * 读取xls文件内容
     * @param file 想要读取的文件对象
     * @return 返回文件内容
     */
    public List<CarPartsInfo> getCarPartsListByJxl(){
    		List<CarPartsInfo> list = new ArrayList<CarPartsInfo>();
		Config.readConfig();
		excelFilePath = Config.exportedFile + ".xls";
		
        FileInputStream inputStream = null;
		try {
			File f = new File("excelFilePath");
			if(!f.exists()){
				System.out.println(f.getAbsolutePath());
				System.out.println(f.getPath());
				System.out.println(f.getParentFile());
			}
			System.out.println(f.getCanonicalPath());
			if(!f.exists()) return null;
			inputStream = new FileInputStream(f);
		} catch (FileNotFoundException e1) {
			log.error(e1);
			return null;
		} catch (IOException e) {
			log.error(e);
			return null;
		}
		jxl.Workbook rwb = null;
        try{   
        		WorkbookSettings workbookSettings = new WorkbookSettings();
            workbookSettings.setEncoding("GBK"); //解决中文乱码，或GBK 
            rwb = jxl.Workbook.getWorkbook(inputStream, workbookSettings);  
            jxl.Sheet firstSheet = rwb.getSheet(0);   
            for (int j = 0; j < firstSheet.getRows(); j++) {  
            		Cell[]  cells = firstSheet.getRow(j);
                CarPartsInfo c = new CarPartsInfo(); 
                if(cells[0] != null && !cells[0].getContents().equals("partno")) {
	    	            c.setPartsNo(cells[PARTS_NO].getContents());
	    	            if(cells[PARTS_NO] != null)
	            			c.setStandardPartsNo(cells[PARTS_NO].getContents());
	    	            
	    	            if(cells[PARTS_NAME] != null)
	    	            		c.setCname(cells[PARTS_NAME].getContents());
	    	            c.setEname(null);
	    	            
	    	            if(cells[PARTS_CAR] != null) 
	    					c.setCarInfo(cells[PARTS_CAR].getContents());
	    				
	    	          //  c.setIsOem(""+( cells[IS_OEM]).getNumericCellValue());
	    	          //  if(!StringUtils.isNotEmpty(c.getIsOem()))
	    	          c.setIsOem("0");
	    	            	
	    	            
	    				if(cells[BRAND] != null) {
	                			c.setManufactureCname(cells[BRAND].getContents().equals("")?"奔驰原厂":cells[BRAND].getContents());
	                			c.setManufactureEname(null);
	    				}
	    				if(cells[PARTS_PRODUCING]!= null) { 
	    					c.setProducingArea(cells[PARTS_PRODUCING].getContents());
	    				}
	    				
	    				if(cells[PARTS_UNIT] != null)
	                			c.setUnit(cells[PARTS_UNIT].getContents());
	    				
	    				if(cells[PARTS_STOCK] != null)
	    					c.setStock(new Long(cells[PARTS_STOCK].getContents()));
	    				if(cells[PARTS_BATCH_PRICE] != null) 
	                			c.setBatchPrice(new Double(cells[PARTS_BATCH_PRICE].getContents()));
	    				if(cells[PARTS_PRICE] != null) 
	    					c.setPrice(new Double(cells[PARTS_PRICE].getContents())); 
	    				c.setCostPrice(0);
	    				c.setProducingArea(cells[PARTS_WAREHOUSE].getContents());
//	    				System.out.println(c.toString());
	    				
	    				//c = brandAnalyze(c);
	    				list.add(c);
                }
            } 
            
        }catch(Exception e){
            e.printStackTrace();
        }
        finally{
			rwb.close();
	        try {
				inputStream.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	        archiveXls();
	    
		}
        return list;
    }
    
    
    /**
     * 读取cvx文件内容
     * @param file 想要读取的文件对象
     * @return 返回文件内容
     */
    public List<CarPartsInfo> getCarPartsListByCSV(){
    		List<CarPartsInfo> list = new ArrayList<CarPartsInfo>();
		Config.readConfig();
		excelFilePath = Config.exportedFile + ".CSV";
		
        FileInputStream inputStream = null;
        File f=null;
		try {
			//D:/PENGYU2018.CSV
			 f = new File(excelFilePath);
			if(!f.exists()){
				
				System.out.println(f.getAbsolutePath());
				System.out.println(f.getPath());
				System.out.println(f.getParentFile());
			}
			System.out.println(f.getCanonicalPath());
			if(!f.exists()) return null;
			inputStream = new FileInputStream(f);
		} catch (FileNotFoundException e1) {
			log.error(e1);
			return null;
		} catch (IOException e) {
			log.error(e);
			return null;
		}
        try{   
        	 // 创建CSV读对象
            CsvReader csvReader = new CsvReader( inputStream,Charset.forName("GBK"));   	
         // 读表头
            csvReader.readHeaders();
            while (csvReader.readRecord()){
                /*// 读一整行
                System.out.println(csvReader.getRawRecord());
                // 读这行的某一列
                System.out.println(csvReader.get("Link"));
                
                System.out.println(csvReader.get(0));
                System.out.println(csvReader.get(1));
                System.out.println(csvReader.get(2));
                System.out.println(csvReader.get(3));*/
                CarPartsInfo c = new CarPartsInfo(); 
                if(csvReader.get(0).equals("partno")) {
                	continue;
                }
	    	            c.setPartsNo(csvReader.get(PARTS_NO));
	    	            if(csvReader.get(PARTS_NO) != null)
	            			c.setStandardPartsNo(csvReader.get(STANDARD_PARTS_NO)  );
	    	            
	    	            if(csvReader.get(PARTS_NAME)  != null)
	    	            		c.setCname(csvReader.get(PARTS_NAME));
	    	            c.setEname(null);
	    	            
	    	            if( csvReader.get(PARTS_CAR) != null) 
	    					c.setCarInfo(csvReader.get(PARTS_CAR));
	    				
	    	        //    c.setIsOem(""+( cells[IS_OEM]).getNumericCellValue());
	    	            if(StringUtils.isNotEmpty(csvReader.get(IS_OEM)))
	    	              c.setIsOem(csvReader.get(IS_OEM));
	    	            else
	    	            	c.setIsOem("0");
	    	            	
	    	            
	    				
	                	c.setManufactureCname(csvReader.get(BRAND));
	                	c.setManufactureEname(null);
	                	c.setManufactureCname(csvReader.get(BRAND).equals("")?"奔驰原厂":csvReader.get(BRAND));
	    			
	    				if(csvReader.get(PARTS_PRODUCING)  != null) { 
	    					c.setProducingArea(csvReader.get(PARTS_PRODUCING));
	    				}
	    				
	    				if(csvReader.get(PARTS_UNIT)   != null)
	                			c.setUnit(csvReader.get(PARTS_UNIT) );
	    				
	    				if(   StringUtils.isNotEmpty(csvReader.get(PARTS_STOCK))){//库存
	    					Double dddd = Double.valueOf( csvReader.get(PARTS_STOCK));
	    			     	DecimalFormat dfd = new DecimalFormat("#");
	    					c.setStock( Long.valueOf(dfd .format(dddd)  ) );
	    				}
	    				if(   StringUtils.isNotEmpty(csvReader.get(PARTS_BATCH_PRICE))){ 
	    					Double dd2= Double.valueOf(csvReader.get(PARTS_BATCH_PRICE) );
	    			     	DecimalFormat df = new DecimalFormat("#.##");
	                		c.setBatchPrice(new Double(df.format(dd2)));
	    				}
	    				if(   StringUtils.isNotEmpty(csvReader.get(PARTS_PRICE))) {
	    					Double dd2= Double.valueOf(csvReader.get(PARTS_PRICE) );
    			     	    DecimalFormat df = new DecimalFormat("#.##");
	    					c.setPrice(new Double(df.format(dd2))); 
	    				}
	    				c.setOwnNo(csvReader.get(PARTS_WAREHOUSE)  );
	    				c.setCostPrice(0);
	    				//c.setOwnNo(cells[PARTS_WAREHOUSE].getContents());
//	    				System.out.println(c.toString());
	    				
	    				//c = brandAnalyze(c);
	    				list.add(c);
                
            }
           // System.out.println(list.size());
            
      
            
            
            
        }catch(Exception e){
            e.printStackTrace();
        }
        finally{
	        try {
				inputStream.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	        archiveXls();
	    
		}
        return list;
    }


	private CarPartsInfo brandAnalyze(CarPartsInfo c) {
		if(c.getStandardPartsNo()!=null) {
			String lastChar = c.getStandardPartsNo().substring(c.getStandardPartsNo().length()-1, c.getStandardPartsNo().length());
			if(lastChar != null && !StringUtils.isNumeric(lastChar)) {
				String brand = StringUtils.substringAfterLast(c.getStandardPartsNo(), " ");
				String stardNo = StringUtils.substringBeforeLast(c.getStandardPartsNo(), " ");
//				System.out.println("brand:" + brand);
//				System.out.println("standardPartNo:" + stardNo);
				c.setManufactureCname(brand);
				c.setStandardPartsNo(stardNo);
			}
		}
		return c;
	}



	public boolean archiveXls() {
	    File file = new File(excelFilePath);
    	
		if(file.exists() && file.delete()){
			log.info(file.getName() + " is deleted!");
		}else{
			log.info("Delete operation is failed for file:" + excelFilePath);
		}
		return false;
	}
	public static void main(String  [] args) {
		SDCarPartsInfoReader reader = new SDCarPartsInfoReader();
		reader.getCarPartsListByJxl();
	}

}
